﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mono.Data;
using Mono.Data.Sqlite;

using System.Data;

namespace Assets.Scripts
{

    public class DatabaseManager : IDisposable
    {
        private string _connectionString = "";
        private IDbConnection _connection = null;
        private IDbCommand _command = null;
        private IDataReader _reader = null;
        
        public DatabaseManager(string connectionString)
        {
            _connectionString = connectionString;
        }

        public DatabaseManager() : this("") { }

        public bool ConnectToDatabase()
        {
            _connection = (IDbConnection)new SqliteConnection(_connectionString);
            _connection.Open();

            if (_connection.State == System.Data.ConnectionState.Open)
            {
                _command = _connection.CreateCommand();
                return true;
            }
            return false;
        }

        public bool ExecuteScript(string script)
        {
            try
            {
                _command.CommandText = script;
                var id = _command.ExecuteNonQuery();
                return true;
            }
            catch (SqliteException ex)
            {
                return false;
            }

        }

        private T CreateObject<T>()
        {
            return (T)Activator.CreateInstance(typeof(T)); ;
        }

        public IEnumerable<T> ReadAll<T>() where T : class
        {
            List<T> result = new List<T>();
            string sql = "SELECT * FROM " + typeof(T).Name;
            _command.CommandText = sql;
            _reader = _command.ExecuteReader();
            var props = typeof(T).GetProperties();
            while (_reader.Read())
            {
                var obj = CreateObject<T>();
                for (int i = 0; i < props.Length; i++)
                {
                    props[i].SetValue(obj, Convert.ChangeType(_reader.GetValue(i),props[i].PropertyType),null);
                }
                 result.Add(obj);
            }
            _reader.Close();
            _reader = null;
            return result;
        }


        public IEnumerable<T> ReadByFieldName<T>(string fieldName, object value) where T : class
        {

            var valueType = value.GetType().Name; 
             string sql ="";
            List<T> result = new List<T>();
            //   T obj = (T)Activator.CreateInstance(typeof(T)); ; // your object
            if(valueType == Types.String.ToString())
                sql = "SELECT * FROM " + typeof(T).Name + " WHERE " + fieldName + " = '" + value + "'";
            else
            sql= "SELECT * FROM " + typeof(T).Name + " WHERE " + fieldName + " = " + value;
            _command.CommandText = sql;
            _reader = _command.ExecuteReader();
            var props = typeof(T).GetProperties();
            while (_reader.Read())
            {
                var obj = CreateObject<T>();
                for (int i = 0; i < props.Length; i++)
                {
                    props[i].SetValue(obj, Convert.ChangeType(_reader.GetValue(i), props[i].PropertyType), null);
                }
                result.Add(obj);
            }
            _reader.Close();
            _reader = null;
            return result;
        }

        public bool CreateTable<T>() where T : class
        {
            string tabInstruction = (new ParseAndGet()).CreateInstruction<T>();
            if (ExecuteScript(tabInstruction))
                return true;
            return false;
        }

        public void InsertRecord<T>(T item) where T : class
        {
           
            string tabInstruction = (new ParseAndGet()).InsertInstruction<T>(item);
            ExecuteScript(tabInstruction);
        }

        public bool DeleteRecordById<T>(object id)
        {
            if(ExecuteScript(new ParseAndGet().RemoveItemsById<T>(id)))
                return true;
            return false;
        }

        public bool DropTable<T>()
        {
            if (ExecuteScript(new ParseAndGet().DropTableInstruction<T>()))
                return true;
            return false;
        }

        public void Dispose()
        {

            _connection.Close();
            _connection = null;
            _command.Dispose();
            _command = null;

        }
    }
}
